package com.cuit.dao;

import com.cuit.domain.ZhangWu;
import com.cuit.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class ZhangWuDao {
    // 获取连接池操作
    private QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());

    public List<ZhangWu> selectAll() {
        String sql = "select * from airui_zhangwu";
        List<ZhangWu> zwList = null;
        try {
            zwList = queryRunner.query(sql, new BeanListHandler<>(ZhangWu.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return zwList;
    }

    public List<ZhangWu> selectByCreatTime(String sDate, String eDate) {
        String sql = "select * from airui_zhangwu where createtime between ? and ?";
        Object[] params = {sDate, eDate};
        List<ZhangWu> zwList = null;
        try {
            zwList = queryRunner.query(sql, new BeanListHandler<>(ZhangWu.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return zwList;
    }

    public int addZhangWu(ZhangWu zhangWu) {
        String sql = "insert into airui_zhangwu values(null, ?, ?, ?, ?, ?)";
        Object[] params = {zhangWu.getFlname(), zhangWu.getMoney(), zhangWu.getZhanghu(), zhangWu.getCreatetime(), zhangWu.getDescription()};
        try {
            return queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<ZhangWu> searchZhangWu(String zw) {
        String sql = "select * from airui_zhangwu where flname like ?";
        List<ZhangWu> zwList = null;
        zw = "%" + zw + "%";
        Object[] params = {zw};
        try {
            zwList = queryRunner.query(sql, new BeanListHandler<>(ZhangWu.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return zwList;
    }

    public int deleteZhangWu(int id) {
        String sql = "delete from airui_zhangwu where zwid = ?";
        Object[] params = {id};
        try {
            return queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int editZhangWu(ZhangWu zhangWu) {
        String sql = "update airui_zhangwu set flname = ?, money = ?, zhanghu = ?, createtime = ?, description = ? where zwid = ?";
        Object[] params = {zhangWu.getFlname(), zhangWu.getMoney(), zhangWu.getZhanghu(), zhangWu.getCreatetime(), zhangWu.getDescription(), zhangWu.getZwid()};
        try {
            return queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
